* This do-file assembles outcomes from FPC data for the output experiment

*****
* FPC Admin Data Inputs
*****
tempfile fpc_inputs

*Kharif 2019*
import excel using "$fpc_raw/Datasheet for Kharif Season_2019.xlsx", clear firstrow sheet(Input Tracker)
format FarmerID %15.0g
rename Village village_name
rename District district_name
rename Block block_name

*Flag crop type (pigeon peas and black gram)
gen byte cropcode = .
replace cropcode=1 if strpos(InputName, "Pigeon Pea")
replace cropcode=4 if strpos(InputName, "Black")

*Input quantities
rename InputQuantity fpc_input_seed
rename LandAllocatedforCultivation fpc_input_area

keep FarmerID cropcode district_name village_name block_name fpc_input_*
gen byte season = 1

save `fpc_inputs', replace

************

*Rabi 2019*
import excel using "$fpc_raw/Data Sheet for Rabi Season_2019.xlsx", clear firstrow sheet(Rabi 2019_All districts)
format FarmerID %15.0g
rename Village village_name
rename District district_name
rename Block block_name
rename Inputname InputName

*Create dummies for crops
gen byte cropcode = .
replace cropcode=2 if strpos(InputName, "GS-10") | strpos(InputName, "GS -10") 		// Green peas
replace cropcode=3 if strpos(InputName, "Lentil") | InputName=="KLS -218" | InputName=="PL-08"
replace cropcode=8 if strpos(InputName, "chickpea") | strpos(InputName, "Chickpea")
replace cropcode=6 if strpos(InputName, "Kidney")

*Input quantities
rename Inputquantity fpc_input_seed
rename LandAllocatedtoCultivation fpc_input_area

keep FarmerID cropcode district_name village_name block_name fpc_input_*
gen byte season = 2

append using `fpc_inputs'
save `fpc_inputs', replace

order district_name block_name village_name FarmerID season cropcode fpc_input*
	// Many duplicates - multiple varieties of seed purchased OR purchased + own seed
collapse (sum) fpc_input*, by(district_name block_name village_name FarmerID season cropcode)
save `fpc_inputs', replace

*****
* FPC Admin Data Outputs
*****

tempfile fpc_outputs

*Black Gram, Kharif 2019
*Merging all 5 regions in one dataset*
import excel "$fpc_raw/Copy of Black Gram Production_26032020.xlsx", clear firstrow sheet(Saran) 
/*no production data in Saran - all listed as damaged by rain*/
destring Plantedareaacres Quantitysownkgs, replace force
rename (Category Village) (Village Category)
drop OtherComment
save `fpc_outputs', replace
import excel "$fpc_raw/Copy of Black Gram Production_26032020.xlsx", clear firstrow sheet(Samastipur)
append using `fpc_outputs', force
save `fpc_outputs',replace
import excel "$fpc_raw/Copy of Black Gram Production_26032020.xlsx", clear firstrow sheet(W.Champaran)
append using `fpc_outputs', force
save `fpc_outputs',replace
import excel "$fpc_raw/Copy of Black Gram Production_26032020.xlsx", clear firstrow sheet(E. Champaran)
rename FarmerUniqueID1 FarmerUniqueID
append using `fpc_outputs', force
save `fpc_outputs',replace
import excel "$fpc_raw/Copy of Black Gram Production_26032020.xlsx", clear firstrow sheet(Siwan)
append using `fpc_outputs', force

rename FarmerUniqueID FarmerID
format FarmerID %15.0g
rename Village village_name
rename District district_name
rename Block block_name
format village_name %27s

rename Quantitysownkgs fpc_outsow_seed
rename Plantedareaacres fpc_outsow_area
recode fpc_outsow_* (.=0)
rename Quantityproducedkg fpc_output_sold

keep FarmerID district_name village_name block_name fpc_out*
gen byte cropcode = 4
gen byte season = 1

save `fpc_outputs', replace

* Lentils, Rabi 2019 PREDICTED OUTPUT
import excel "$fpc_raw/All_Procurement_Lentil.xlsx", clear firstrow
rename Village village_name
rename District district_name
rename Block block_name
format village_name %27s

* Create new unique value for added farmers
destring FarmerID, replace force
format FarmerID %15.0g
sort FarmerID
// ids 100--800 unassigned
replace FarmerID = _n+100 if missing(FarmerID)

rename U fpc_outsow_area
destring P S, replace force
egen fpc_outsow_seed = rowtotal(QuantityInKg P S)
gen fpc_output_prod = subinstr(TotalEstimatedProduction,"kg","",.)
destring fpc_output_prod, replace
rename NetWeightKg fpc_output_sold

* Fix 2 double entries
collapse (sum) fpc_outsow_* fpc_output_*, by(FarmerID district_name village_name block_name)

gen byte cropcode = 3
gen byte season = 2

append using `fpc_outputs'
save `fpc_outputs', replace


merge 1:1 FarmerID season cropcode using `fpc_inputs', update replace
recode fpc_output_* (.=0) if (season==1 & cropcode==4 & district_name!="Saran") | (season==2 & cropcode==3) 
drop _merge

* Fix naming errors
replace district_name="East Champaran" 	if district_name=="East Chamaparan"
replace block_name="Pakri Dayal" 	if block_name=="Pakridayal"
replace block_name="Chiraia" 		if block_name=="Chiraiya"
replace village_name="Kachnar" 		if village_name=="Kachnar (Siwan)" | village_name=="Kachanr "
replace village_name="Mathiya" 		if village_name=="Mathiya (West Champaran)"
replace village_name="Sirisiya" 	if village_name=="Sirisiya (West Champaran)"
replace village_name="Uttar Tola" 	if village_name=="Uttar Tola, Tharbhitia"
replace village_name = "Man Tola" 	if village_name == "Man tola"
replace village_name="Akaunadih" 	if village_name=="Akaunadeeh"
replace village_name="Bahuarwa" 	if village_name=="Bahuarba"
replace village_name="Jaipal Tola" 	if village_name=="Jaipaltola"
replace village_name="Jitwariya" 	if village_name=="Jitwaria"
replace village_name="Lakshrampur" 	if village_name=="Lakshyarampur"
replace village_name="Jae Chapra" 	if village_name=="Jai Chapra"
replace village_name="Gagdishpur Rani"  if village_name==" Jagdispur Rani"
replace village_name="Gagatsinghpur" 	if village_name=="Jagatsinghpur"


order district_name block_name village_name FarmerID season cropcode fpc_input* fpc_outsow* fpc_output*
save "$out_data/fpc_procurement.dta", replace


